import numpy as np
import pandas as pd
from sklearn.feature_extraction.text import TfidfVectorizer
from sklearn.metrics.pairwise import cosine_similarity
from utils.db import get_mysql_conn, get_redis_conn
import json
from datetime import datetime
import traceback

class ContentBasedRecommender:
    """改进的基于内容的推荐算法，增加了错误处理和调试信息"""
    
    def __init__(self):
        self.mysql_conn = get_mysql_conn()
        self.redis_conn = get_redis_conn()
        self.courses_df = None
        self.tfidf_matrix = None
        self.course_similarity = None
        self.course_id_map = {}
        self.reverse_course_map = {}
    
    def load_data_from_db(self):
        """从数据库加载课程数据"""
        print("从数据库加载课程数据...")
        
        try:
            # 确保数据库连接有效
            if not self.mysql_conn or not self._test_connection(self.mysql_conn):
                self.mysql_conn = get_mysql_conn()
                
            if not self.mysql_conn:
                raise Exception("无法建立数据库连接")
            
            # 先检查courses表的结构
            with self.mysql_conn.cursor() as cursor:
                cursor.execute("DESCRIBE courses")
                columns = cursor.fetchall()
                print("Courses表的列结构:")
                for col in columns:
                    print(f"  {col}")
                
                # 获取一条记录
                cursor.execute("SELECT * FROM courses LIMIT 1")
                sample = cursor.fetchone()
                print("样本数据:")
                for k, v in sample.items():
                    print(f"  {k}: {v} (类型: {type(v)})")
                
            # 加载课程数据 - 适应现有的数据库结构
            query = """
            SELECT c.id, c.title, cat.name as category, c.tags, c.description, 
                   t.name as teacher_name, c.teacher_id
            FROM courses c
            LEFT JOIN categories cat ON c.category_id = cat.id
            LEFT JOIN teachers t ON c.teacher_id = t.id
            """
            
            # 使用原生游标获取数据
            with self.mysql_conn.cursor() as cursor:
                cursor.execute(query)
                data = cursor.fetchall()
                # 转换为DataFrame
                self.courses_df = pd.DataFrame(data)
            
            print(f"DataFrame信息:")
            print(f"  形状: {self.courses_df.shape}")
            print(f"  列名: {self.courses_df.columns.tolist()}")
            print(f"  数据类型:\n{self.courses_df.dtypes}")
            
            # 打印前几行
            print("前几行数据:")
            print(self.courses_df.head())
            
            # 处理空值
            text_columns = ['title', 'category', 'tags', 'description']
            for col in text_columns:
                if col in self.courses_df.columns:
                    self.courses_df[col] = self.courses_df[col].fillna('')
            
            # 添加一个keywords列（如果没有）
            if 'keywords' not in self.courses_df.columns:
                self.courses_df['keywords'] = self.courses_df['tags']
            
            print(f"从数据库加载了 {len(self.courses_df)} 门课程")
            return True
            
        except Exception as e:
            print(f"从数据库加载课程数据失败: {e}")
            traceback.print_exc()
            return False
    
    def _test_connection(self, conn):
        """测试数据库连接是否有效"""
        try:
            with conn.cursor() as cursor:
                cursor.execute("SELECT 1")
                cursor.fetchone()
            return True
        except Exception:
            return False
    
    def preprocess_data(self):
        """预处理课程数据，准备用于TF-IDF分析"""
        if self.courses_df is None:
            print("请先加载课程数据")
            return False
            
        print("预处理课程数据...")
        
        # 确保必要的列存在
        required_columns = ['id', 'title', 'category', 'tags', 'description']
        for col in required_columns:
            if col not in self.courses_df.columns:
                print(f"缺少必要的列: {col}")
                return False
                
        # 确保ID是整数类型
        try:
            self.courses_df['id'] = self.courses_df['id'].astype(int)
        except Exception as e:
            print(f"转换课程ID为整数时出错: {e}")
            print(f"课程ID数据类型: {self.courses_df['id'].dtype}")
            print(f"ID样本值: {self.courses_df['id'].head()}")
            return False
        
        # 创建课程ID映射
        self.course_id_map = {id: idx for idx, id in enumerate(self.courses_df['id'])}
        self.reverse_course_map = {idx: id for id, idx in self.course_id_map.items()}
        
        print(f"创建了{len(self.course_id_map)}个课程ID映射")
        
        # 组合课程特征文本，用于TF-IDF分析
        # 根据重要性加权，重复更重要的特征
        self.courses_df['content'] = (
            self.courses_df['title'] + ' ' + self.courses_df['title'] + ' ' +  # 标题重复两次
            self.courses_df['category'].fillna('') + ' ' +  # 类别
            self.courses_df['tags'].fillna('') + ' ' +  # 标签
            self.courses_df['description'].fillna('') + ' ' +  # 描述
            self.courses_df['keywords'].fillna('')   # 关键词
        )
        
        print("课程数据预处理完成")
        return True
    
    def build_tfidf_matrix(self):
        """构建TF-IDF矩阵"""
        if 'content' not in self.courses_df.columns:
            print("请先预处理课程数据")
            return False
            
        print("构建TF-IDF矩阵...")
        
        try:
            # 创建TF-IDF向量化器
            tfidf = TfidfVectorizer(
                analyzer='word',
                ngram_range=(1, 2),    # 1-gram和2-gram
                min_df=1,              # 至少出现在1个文档中
                max_features=2000      # 最多2000个特征
            )
            
            # 转换课程内容为TF-IDF矩阵
            self.tfidf_matrix = tfidf.fit_transform(self.courses_df['content'])
            
            # 计算相似度矩阵
            self.course_similarity = cosine_similarity(self.tfidf_matrix)
            
            print(f"TF-IDF矩阵构建完成，大小: {self.tfidf_matrix.shape}")
            return True
        except Exception as e:
            print(f"构建TF-IDF矩阵失败: {e}")
            traceback.print_exc()
            return False
    
    def save_similarity_to_redis(self):
        """将课程相似度保存到Redis"""
        if self.course_similarity is None:
            print("请先构建TF-IDF矩阵")
            return False
            
        print("保存内容相似度到Redis...")
        
        try:
            if not self.redis_conn:
                self.redis_conn = get_redis_conn()
                
            # 使用管道批量写入Redis
            pipeline = self.redis_conn.pipeline()
            
            # 设置过期时间（一天）
            expiry = 60 * 60 * 24
            
            # 记录更新时间
            update_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            pipeline.set("content_based:update_time", update_time)
            
            # 保存相似度矩阵
            saved_count = 0
            
            for i, course_id in self.reverse_course_map.items():
                try:
                    # 获取与该课程相似的其他课程及相似度
                    similarities = {}
                    for j, sim in enumerate(self.course_similarity[i]):
                        if i != j and sim > 0:  # 排除自身，只保存相似度大于0的
                            similar_id = self.reverse_course_map[j]
                            if isinstance(similar_id, (int, np.integer)):
                                similarities[str(similar_id)] = float(sim)
                            else:
                                print(f"警告: 相似课程ID不是整数: {similar_id}, 类型: {type(similar_id)}")
                    
                    # 对每个课程，只保存相似度最高的N个课程
                    top_similar = sorted(similarities.items(), key=lambda x: x[1], reverse=True)[:20]
                    
                    # 将相似度转换为JSON格式并保存
                    if top_similar:  # 只有在有相似课程时才保存
                        key = f"content_based:similarity:{int(course_id)}"
                        value = json.dumps({k: float(v) for k, v in dict(top_similar).items()})
                        pipeline.set(key, value, ex=expiry)
                        saved_count += 1
                except Exception as e:
                    print(f"处理课程 {course_id} 的相似度时出错: {e}")
                    traceback.print_exc()
                    continue
            
            # 执行管道命令
            if saved_count > 0:
                pipeline.execute()
                print(f"成功保存内容相似度到Redis，共 {saved_count} 个课程")
                return True
            else:
                print("没有找到任何有效的课程相似度数据可保存")
                return False
            
        except Exception as e:
            print(f"保存内容相似度到Redis失败: {e}")
            traceback.print_exc()
            return False
    
    def train_model(self):
        """训练模型，计算内容相似度并保存到Redis"""
        # 重新获取数据库连接
        from utils.db import get_mysql_conn, get_redis_conn
        self.mysql_conn = get_mysql_conn(force_new=True)
        self.redis_conn = get_redis_conn(force_new=True)
        
        # 从数据库加载数据
        if not self.load_data_from_db():
            print("加载数据失败，无法训练模型")
            return False
        
        # 预处理数据
        if not self.preprocess_data():
            print("预处理数据失败，无法训练模型")
            return False
        
        # 构建TF-IDF矩阵
        if not self.build_tfidf_matrix():
            print("构建TF-IDF矩阵失败，无法训练模型")
            return False
        
        # 保存相似度到Redis
        return self.save_similarity_to_redis()

    def load_similarity_from_redis(self):
        """从Redis加载课程内容相似度"""
        print("从Redis加载内容相似度...")
        
        try:
            if not self.redis_conn:
                self.redis_conn = get_redis_conn()
                
            # 获取更新时间
            update_time = self.redis_conn.get("content_based:update_time")
            if update_time:
                # 尝试解码，如果已经是字符串则直接使用
                if isinstance(update_time, bytes):
                    update_time = update_time.decode('utf-8')
                print(f"内容相似度数据的更新时间: {update_time}")
            
            # 获取所有课程相似度键
            similarity_keys = self.redis_conn.keys("content_based:similarity:*")
            
            if not similarity_keys:
                print("Redis中没有找到内容相似度数据")
                return False
            
            # 清空当前相似度矩阵
            self.content_similarity = {}
            
            # 加载相似度数据
            for key in similarity_keys:
                try:
                    # 解析键
                    key_str = key
                    if isinstance(key, bytes):
                        key_str = key.decode('utf-8')
                    
                    # 提取课程ID
                    course_id = int(key_str.split(":")[-1])
                    
                    # 获取相似度数据
                    similarity_json = self.redis_conn.get(key)
                    if similarity_json:
                        # 解码为字符串（如果需要）
                        if isinstance(similarity_json, bytes):
                            similarity_json = similarity_json.decode('utf-8')
                        
                        # 解析JSON数据
                        similarity_dict = json.loads(similarity_json)
                        
                        # 确保所有键都是字符串，值都是浮点数
                        self.content_similarity[course_id] = {str(k): float(v) for k, v in similarity_dict.items()}
                except Exception as e:
                    print(f"处理键 {key} 时出错: {e}")
                    traceback.print_exc()
                    continue
            
            print(f"从Redis加载了 {len(self.content_similarity)} 个课程的内容相似度数据")
            return True
            
        except Exception as e:
            print(f"从Redis加载内容相似度失败: {e}")
            traceback.print_exc()
            return False

    def get_user_interests(self, user_id):
        """获取用户兴趣标签"""
        try:
            if not self.mysql_conn:
                self.mysql_conn = get_mysql_conn()
                
            # 查询用户感兴趣的内容 - 适应现有的数据库结构
            query = """
            SELECT c.id, c.title, cat.name as category, c.tags,
                   CASE 
                       WHEN b.event_type = 'rate' AND JSON_EXTRACT(b.extra_data, '$.rating') IS NOT NULL 
                       THEN CAST(JSON_EXTRACT(b.extra_data, '$.rating') AS DECIMAL(3,1))
                       WHEN b.event_type = 'complete' THEN 5.0
                       WHEN b.event_type = 'favorite' THEN 4.0
                       ELSE 3.0
                   END AS user_preference
            FROM user_behaviors b
            JOIN courses c ON b.course_id = c.id
            LEFT JOIN categories cat ON c.category_id = cat.id
            WHERE b.user_id = %s 
              AND (
                (b.event_type = 'rate' AND CAST(JSON_EXTRACT(b.extra_data, '$.rating') AS DECIMAL(3,1)) >= 4.0)
                OR b.event_type IN ('complete', 'favorite')
              )
            ORDER BY b.timestamp DESC
            LIMIT 10
            """
            
            # 使用原生游标查询
            with self.mysql_conn.cursor() as cursor:
                cursor.execute(query, (user_id,))
                user_courses_data = cursor.fetchall()
                
            # 如果没有找到用户的兴趣数据
            if not user_courses_data:
                print(f"用户 {user_id} 没有找到足够的兴趣数据")
                return None
            
            # 提取用户的兴趣标签
            categories = []
            tags = []
            recent_courses = []
            
            for course in user_courses_data:
                course_id = course['id']
                recent_courses.append(course_id)
                
                if course['category']:
                    categories.append(course['category'])
                
                if course['tags']:
                    tags.extend([tag.strip() for tag in course['tags'].split(',')])
            
            # 统计频率
            from collections import Counter
            category_counter = Counter(categories)
            tag_counter = Counter(tags)
            
            # 提取最频繁出现的兴趣标签
            top_categories = [category for category, _ in category_counter.most_common(3)]
            top_tags = [tag for tag, _ in tag_counter.most_common(5)]
            
            # 组合用户兴趣标签
            user_interests = {
                'categories': top_categories,
                'tags': top_tags,
                'recent_courses': recent_courses
            }
            
            return user_interests
            
        except Exception as e:
            print(f"获取用户兴趣标签失败: {e}")
            traceback.print_exc()
            return None
    
    def recommend_by_content(self, user_id, n=10, exclude_courses=None):
        """基于内容为用户推荐课程"""
        # 初始化排除课程列表
        if exclude_courses is None:
            exclude_courses = []
        
        # 获取用户兴趣标签
        user_interests = self.get_user_interests(user_id)
        if not user_interests:
            print(f"无法获取用户 {user_id} 的兴趣标签")
            return []
        
        # 获取用户最近的课程
        recent_courses = user_interests['recent_courses']
        exclude_courses.extend(recent_courses)
        
        # 如果有内容相似度数据（从Redis加载的）
        if hasattr(self, 'content_similarity') and self.content_similarity:
            print("使用Redis中的内容相似度数据进行推荐")
            
            # 计算候选课程的分数
            candidate_scores = {}
            
            # 基于用户最近课程的相似课程推荐
            for course_id in recent_courses:
                if course_id in self.content_similarity:
                    similar_courses = self.content_similarity[course_id]
                    for similar_id_str, similarity in similar_courses.items():
                        similar_id = int(similar_id_str)
                        if similar_id not in exclude_courses:
                            if similar_id not in candidate_scores:
                                candidate_scores[similar_id] = 0
                            candidate_scores[similar_id] += similarity
            
            # 排序并返回推荐结果
            recommendations = sorted(candidate_scores.items(), key=lambda x: x[1], reverse=True)[:n]
            
            return recommendations
        
        # 如果没有从Redis加载数据，使用TF-IDF矩阵进行推荐
        elif hasattr(self, 'tfidf_matrix') and self.tfidf_matrix is not None and hasattr(self, 'course_id_map'):
            print("使用TF-IDF矩阵进行推荐")
            
            # 如果没有最近课程，无法推荐
            if not recent_courses:
                print("用户没有最近的课程，无法基于内容推荐")
                return []
            
            # 计算候选课程的分数
            candidate_scores = {}
            
            for course_id in recent_courses:
                if course_id in self.course_id_map:
                    idx = self.course_id_map[course_id]
                    similarities = self.course_similarity[idx]
                    
                    for j, sim in enumerate(similarities):
                        similar_id = self.reverse_course_map[j]
                        if similar_id not in exclude_courses and similar_id != course_id:
                            if similar_id not in candidate_scores:
                                candidate_scores[similar_id] = 0
                            candidate_scores[similar_id] += sim
            
            # 排序并返回推荐结果
            recommendations = sorted(candidate_scores.items(), key=lambda x: x[1], reverse=True)[:n]
            
            return recommendations
        
        # 如果没有相似度数据，尝试使用兴趣标签匹配
        else:
            print("使用兴趣标签匹配进行推荐")
            
            try:
                # 从数据库查询匹配用户兴趣的课程
                if not self.mysql_conn:
                    self.mysql_conn = get_mysql_conn()
                
                # 构建查询条件
                categories = user_interests['categories']
                tags = user_interests['tags']
                
                # 构建SQL查询
                query = """
                SELECT c.id, c.title, cat.name as category, c.tags, 
                       CASE 
                           WHEN cat.name IN ({cat_placeholders}) THEN 2 
                           ELSE 0 
                       END +
                       CASE 
                           WHEN {tag_conditions} THEN 1 
                           ELSE 0 
                       END AS match_score
                FROM courses c
                LEFT JOIN categories cat ON c.category_id = cat.id
                WHERE c.id NOT IN ({exclude_placeholders})
                HAVING match_score > 0
                ORDER BY match_score DESC, c.enroll_count DESC
                LIMIT %s
                """
                
                # 准备参数
                params = []
                
                # 类别参数
                if categories:
                    cat_placeholders = ', '.join(['%s'] * len(categories))
                    params.extend(categories)
                else:
                    cat_placeholders = "''"
                
                # 标签参数
                if tags:
                    tag_conditions = ' OR '.join(['c.tags LIKE %s'] * len(tags))
                    params.extend([f'%{tag}%' for tag in tags])
                else:
                    tag_conditions = "FALSE"
                
                # 排除课程参数
                if exclude_courses:
                    exclude_placeholders = ', '.join(['%s'] * len(exclude_courses))
                    params.extend(exclude_courses)
                else:
                    exclude_placeholders = "-1"  # 不排除任何课程
                
                # 添加限制数量参数
                params.append(n)
                
                # 替换查询中的占位符
                query = query.format(
                    cat_placeholders=cat_placeholders,
                    tag_conditions=tag_conditions,
                    exclude_placeholders=exclude_placeholders
                )
                
                # 执行查询
                with self.mysql_conn.cursor() as cursor:
                    cursor.execute(query, params)
                    matching_courses = cursor.fetchall()
                
                # 返回推荐结果
                recommendations = [(row['id'], row['match_score']) for row in matching_courses]
                
                return recommendations
                
            except Exception as e:
                print(f"使用兴趣标签匹配进行推荐失败: {e}")
                traceback.print_exc()
                return []

# 测试代码
if __name__ == "__main__":
    # 创建基于内容的推荐器实例
    content_recommender = ContentBasedRecommender()
    
    # 训练模型
    success = content_recommender.train_model()
    
    print(f"模型训练{'成功' if success else '失败'}") 